﻿using BookManege.controller;
using BookManege.entity;
using MySql.Data.MySqlClient;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

namespace BookManege.service
{

    internal class BookManegeService
    {
        private MySqlConnection MySqlConnection;

        public BookManegeService()
        {
            this.MySqlConnection = DatabaseUtil.GetMysqlConnection();
        }
        public int GetCount()
        {
            return Convert.ToInt32(DatabaseUtil.SelectUtil("SELECT sum(number) FROM book"));
        }
        public DataTable GetBookList(int pageNum,int pageSize)
        {
            //1 10 
            //一 0 10  二 10 10  三 20  10
            //pageNum (pageNum-1)*pageSize pageSize
            return DatabaseUtil.GetDataTable("SELECT id,img_url,book_name,auther,ICBN,price,state,number,join_date FROM book limit " + ((pageNum - 1) * pageSize) + "," + pageSize);
        }
        public DataTable GetBookListByUser(int pageNum, int pageSize)
        {
            return DatabaseUtil.GetDataTable("SELECT id,book_name,auther,ICBN,price,number FROM book limit " + ((pageNum - 1) * pageSize) + "," + pageSize);
        }
        public int InsertBook(Book book)
        {
            return DatabaseUtil.MyExecuteNonQuery("insert into book(book_name,auther,img_url,ICBN,price,number,join_date) values ('" + book.BookName + "','" + book.Aurher + "','" +
               book.ImgUrl+"','"+book.Icbn + "'," + book.Price + "," + book.Number + ",CURDATE())");
        }
        public int DeleteById(string id)
        {
            return DatabaseUtil.MyExecuteNonQuery("delete from book where id = '" + id + "'");
        }
        public int Update(Book book)
        {
            return DatabaseUtil.MyExecuteNonQuery("update book set book_name = '" + book.BookName + "',auther = '" + book.Aurher + "',img_url='"+book.ImgUrl+"',ICBN = '" + book.Icbn +
                "',price = '" + book.Price + "',state = '" + book.State + "',number = '" + book.Number + "' where id = " + book.Id);
        }
        public DataTable search(string bookName, string bookAuther, string iCBN, DateTime startDate,DateTime endDate)
        {
            return DatabaseUtil.GetDataTable("select id,img_url,book_name,auther,ICBN,price,state,number,join_date from book where 1=1 " +
                (bookName != "" ? " and book_name like '%" + bookName + "%'" : "") +
                (bookAuther != "" ? " and auther like '%" + bookAuther + "%'" : "") +
                (iCBN != "" ? " and ICBN = " + iCBN : "") +
                " and join_date >='" + startDate + "' and join_date<='" + endDate + "'");
        }
        public DataTable search(string bookName, string bookAuther)
        {
            return DatabaseUtil.GetDataTable("select * from book where 1=1 " +
                (bookName != "" ? " and book_name like '%" + bookName + "%'" : "") +
                (bookAuther != "" ? " and auther like '%" + bookAuther + "%'" : ""));
        }
    }
}
